import math
import os
import time
import sys
import openpyxl

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.db.CnabsDb import CnabsDb
from src.utils import CommonUtils, BatchUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


def main():
    logger = Logger()
    cleanScreen()
    t = time.perf_counter()

    logger.info("========中诚信行业编码导入 开始 ========")
    workbook = openpyxl.load_workbook("d://中诚信行业分类编码表.xlsx")

    # 获取当前活动工作表（默认为第一张）
    sheet = workbook.active
    # 遍历每一行并输出 A 列的值
    i = 0
    dataInfo = []
    updateDatas = []
    for row in sheet.iter_rows(min_row=1, min_col=2, max_row=sheet.max_row, max_col=2, values_only=True):
        if i > 0:
            for cell in row:
                if cell is not None and cell != '*':
                    dataInfo.append(cell)
        i = i + 1
    datalen = dataInfo.__len__()
    for num in range(0,datalen-1,2):
        updateDatas.append((
            dataInfo[num+1],
            dataInfo[num],
            0,
            0
        ))
    BatchUtils.batchInsert(codeUpdates ,updateDatas)


    # 遍历每一行并输出 二级 列的值
    i = 0
    dataInfo = []
    updateDatas = []
    for row in sheet.iter_rows(min_row=1, min_col=4, max_row=sheet.max_row, max_col=4, values_only=True):
        if i > 0:
            for cell in row:
                if cell is not None and cell != '*':
                    dataInfo.append(cell[0:7])
                    dataInfo.append(cell[7:20])
        i = i + 1
    datalen = dataInfo.__len__()
    for num in range(0,datalen-1,2):
        updateDatas.append((
            dataInfo[num],
            dataInfo[num+1],
            dataInfo[num][0:3]+"0000",
            1
        ))
    BatchUtils.batchInsert(codeUpdates ,updateDatas)


    # 遍历每一行并输出 三级 列的值
    i = 0
    dataInfo = []
    updateDatas = []
    for row in sheet.iter_rows(min_row=1, min_col=6, max_row=sheet.max_row, max_col=6, values_only=True):
        if i > 0:
            for cell in row:
                if cell is not None and cell != '*':
                    dataInfo.append(cell[0:7])
                    dataInfo.append(cell[7:20])
        i = i + 1
    datalen = dataInfo.__len__()
    for num in range(0,datalen-1,2):
        updateDatas.append((
            dataInfo[num],
            dataInfo[num+1],
            dataInfo[num][0:5]+"00",
            2
        ))
    BatchUtils.batchInsert(codeUpdates ,updateDatas)

def codeUpdates(updateDatas):
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    sql = """
    REPLACE INTO `ccx_industry_code` (`code`, `name`, `parent_code`, `level`, `deltet_flag`, `update_user`) VALUES 
    (%s, %s, %s, %s, 0 , 'system');
    """
    return dataCenterDb.ExecMany(sql, updateDatas)

# 程序入口
if __name__ == "__main__":
    main()
